-- @owner: li-xin12345
-- @date: 2023/7/13
-- @testpoint: ustore HASH分区表，非压缩表改为压缩表
-- @history: Modified by li-xin12345 2024/8/13: 研发代码变更，toast表默认btree改成ubtree,增加索引fsm文件，及填充因子变化，导致表大小变化
-- @history: Modified by li-xin12345 2024/9/20: 研发代码变更，填充因子变化，导致表大小变化，优化用例，建表时指定填充因子
-- @history: Modified by li-xin12345 2025.2.12：研发代码变更，取消参数控制，适配用例

--step1:建ustore HASH分区表并插入数据;expect:成功
drop table if exists t_alter_rowcompress_0096 cascade;
create table t_alter_rowcompress_0096 (col1 int, col2 int) with (storage_type = ustore, fillfactor = 100)
partition by hash(col1)
(
partition p1,
partition p2
);

declare
    i integer;
begin
    for i in 1..10 loop
    	insert into t_alter_rowcompress_0096 values(1, generate_series(0, 500));
        insert into t_alter_rowcompress_0096 values(2, generate_series(0, 500));
        insert into t_alter_rowcompress_0096 values(3, generate_series(0, 500));
        insert into t_alter_rowcompress_0096 values(4, generate_series(0, 500));
    end loop;
end;
/

--step2:查看分区表信息;expect:成功
select relname, reloptions from pg_partition order by relname;

--step3:查看表文件占用磁盘大小;expect:成功
select pg_size_pretty(pg_total_relation_size('t_alter_rowcompress_0096')) as table_size;

--step4:修改表为压缩表，checkpoint;expect:成功
alter table t_alter_rowcompress_0096 set (compresstype = 2);
checkpoint;

--step5:再次查看表文件占用磁盘大小;expect:表文件磁盘占用小于非压缩表
select pg_size_pretty(pg_total_relation_size('t_alter_rowcompress_0096')) as table_size;

--step6:查看压缩率;expect:成功
drop function if exists compress_func_findpath(character varying);
create or replace function compress_func_findpath(character varying)
returns character varying
language plpgsql
as
$body$
declare
relpath character varying;
begin
relpath = (select pg_relation_filepath(relname::regclass) from pg_class where relname = $1);
return relpath;
end;
$body$;
/
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0096'));

--step7:查看分区表信息;expect:成功
select relname, reloptions from pg_partition order by relname;

--step8:清理环境;expect:成功
drop function if exists compress_func_findpath(character varying);
drop table if exists t_alter_rowcompress_0096;